package cn.edu.seu.herald.informationplatform.dao.database;

import java.util.HashMap;
import java.util.Map;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

import cn.edu.seu.herald.informationplatform.bean.league.Album;

public class AlbumDao extends DataAccessObject{
    private static AlbumDao daoInstance=null;
	protected AlbumDao(){
		
	}
	
	public static AlbumDao getDao(){
		if(daoInstance==null){
			daoInstance=new AlbumDao();
		}
		return daoInstance;
	}
	//judge have id
	public boolean hasId(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps = con.prepareStatement("Select * from lg_album where id=?");
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			int i = 0;
			while(rs.next()){
			i = rs.getRow();
			}
			closeRs(rs);
			if(i>0){
			return true;
			}else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	//add
	//Return:the id of the new item
	public int addAlbum(Album album) throws SQLException{
		Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("insert into lg_album(name,coverpic_address,league_id) values (?,?,?)",Statement.RETURN_GENERATED_KEYS);
    	ps.setString(1,album.getName());
    	ps.setString(2,album.getCoverPicAddress());
    	ps.setInt(3,album.getLeagueId());
    	ps.executeUpdate();
    	ResultSet rsResultSet=ps.getGeneratedKeys();
		int new_id=-1;
		if(rsResultSet.next()){
			new_id=rsResultSet.getInt(1);
		}
		closeRs(rsResultSet);
		return new_id;
    	}catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
	//delete
	public void deleteAlbum(int id) throws SQLException{
		Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("delete from lg_album where id=?");
    	ps.setInt(1,id);
    	ps.executeUpdate();
    	}catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	//update
	public void updateAlbum(int id,Album newAlbum) throws SQLException{
		Connection con=getConnectionLeague();
		try {
				PreparedStatement ps=con.prepareStatement("update lg_album set name=?,coverpic_address=?,league_id=? where id=?");
				ps.setString(1,newAlbum.getName());
				ps.setString(2,newAlbum.getCoverPicAddress());
				ps.setInt(3,newAlbum.getLeagueId());
				ps.setInt(4,id);
				ps.executeUpdate();
				
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	//getLeagueAlbums by leagueId
	//Return:Map<id,AlbumObject>
    public Map<Integer,Album> getAlbumsByLeagueId(int leagueId) throws SQLException{
    	Connection con=getConnectionLeague();
    	HashMap<Integer,Album> idAlbumMap=new HashMap<Integer, Album>();
    	try{
    	PreparedStatement ps=con.prepareStatement("select * from lg_album where league_id=? ");
    	ps.setInt(1,leagueId);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		Album album=new Album();
    		album.setCoverPicAddress(rsResultSet.getString(3));
    		album.setLeagueId(rsResultSet.getInt(4));
    		album.setName(rsResultSet.getString(2));
    		idAlbumMap.put(rsResultSet.getInt(1),album);
    	}
    	closeRs(rsResultSet);
    	return idAlbumMap;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
    
  //getAlbum
    public Album getAlbumById(int AlbumId) throws SQLException{
    	Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("select * from lg_album where id=?");
    	ps.setInt(1,AlbumId);
    	ResultSet rsResultSet=ps.executeQuery();
    	Album album=new Album();
    	while(rsResultSet.next()){
    		album.setCoverPicAddress(rsResultSet.getString(3));
    		album.setLeagueId(rsResultSet.getInt(4));
    		album.setName(rsResultSet.getString(2));
    	}
    	closeRs(rsResultSet);
    	return album;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
}
